The invention relates to database processing method and apparatus and, more particularly, to database processing method and apparatus which can define a Large OBject data type (LOB type) and an Abstract Data Type (ADT type).
A database management system (hereinbelow, abbreviated to DBMS), particularly, a relational DBMS processes an inquiry expressed by a non-procedure like language. SQL is used as a database language. With respect to the database language SQL, studies regarding the standard of the next SQL (ISO, ANSI) are being progressed at present.
The LOB type is one of main functions of the next SQL. The LOB type is a type which enables the DBMS to handle data such as document, moving picture, audio data, or the like of an amount that is extremely larger than that of data which has hitherto been handled by a conventional database. In accordance with a type of data to be stored, a CLOB type for storing character data such as characters or the like and a BLOB type for storing binary data such as image, audio, or the like are prepared.
As for data such as moving picture, audio, or the like, since a size of each data is large, data of a few megabytes or gigabytes is also considered. It is difficult for the user to store or read out data of the LOB type to/from the DBMS in a lump. Therefore, an interface to divisionally write or read out data by a plurality of operations is generally used.
An example of the operation for such LOB data is shown below. An LOB locator is used in order to hold the LOB data as variables in the DBMS and to operate the LOB data by an UAP (user application). Operation examples are shown by a description by a C language. A sentence structure which begins with "EXEC SQL" buried in the C language program is a description of the DBMS operation.
EXEC SQL BEGIN DECLARE SECTION;
SQL TYPE IS BLOB LOCATOR resume; PA1 char buffer[1000]=""; PA1 int number; PA1 int start; PA1 int end; PA1 SELECT no, resume FROM emp.sub.13 resume WHERE PA1 format=`ascii`; PA1 :resume); PA1 (rooms INTEGER, PA1 size DECIMAL(8,2), PA1 text.sub.13 description VARCHAR(1024), PA1 location address, PA1 document doc); PA1 (street CHAR(30), PA1 city CHAR(20), PA1 state CHAR(2), PA1 zip INTEGER); PA1 (format CHAR(20), PA1 data BLOB(10M)); PA1 (price INTEGER, PA1 owner CHAR(40), PA1 property real.sub.13 estate); PA1 SELECT property FROM real.sub.13 estate.sub.13 info PA1 a) preparing a handle (FIG. 4) serving as a control block for managing a storage area of data as a processing target; PA1 b) when the data as a processing target corresponding to the processing request is processed in accordance with the processing request, receiving and transmitting the handle regarding the processing target data corresponding to the processing request between the request accepting server and the database processing server to which the above process was allocated and executing a process according to the processing request; and PA1 c) obtaining an entity of the processing target data on the basis of the handle when the entity of the processing target data is necessary in the step b) and processing. PA1 a) preparing a server which is a control block for managing a storage area of data as a processing target and in which the data as a processing target has been stored and a handle having information with respect to a storing location; PA1 b) when the data as a processing target corresponding to the processing request is processed in accordance with the processing request, executing a process according to the processing request by the request accepting server by using the handle regarding the processing target data; and PA1 c) when an entity of the processing target data is necessary in the step b), obtaining the entity of the processing target data on the basis of the handle and processing. PA1 in the step a), as such a handle, a handle having a server identifier to identify the server in which the data as a processing target corresponding to the processing request has been stored and storing position information indicative of a position in the server at which the processing target data has been stored is prepared; PA1 in the step b), the data as a processing target corresponding to the processing request is designated by using a locator which is associated in the processing request and designates the handle as a parameter; and PA1 in the step c), when the entity of the processing target data is necessary, the entity of the processing target data which is identified by the server identifier and storing position information of the handle is obtained with reference to the handle that is designated by the locator. PA1 as a location to store the data of the predetermined type, there are a memory in the server, a temporary file of an external storage device connected to the server, or a database of the external storage device connected to the server; and PA1 in the step a), as the storing position information, a handle having a flag indicating in which one of the memory in the server, the temporary file of the external storage device connected to the server, and the database of the external storage device connected to the server the data has been stored and an address in which the data has been stored is prepared.
EXEC SQL END DECLARE SECTION;
In the above description, variables defined within a range from "SQL BEGIN DECLARE SECTION" to "END DECLARE SECTION" denote a definition of the variables which are used for the DB operation. "resume" denotes the LOB locator.
EXEC SQL DECLARE cl CURSOR FOR
EXEC SQL OPEN cl;
EXEC SQL FETCH cl INTO :number, :resume;
It denotes a description indicating that a retrieval for obtaining values of "no" and "resume" in the line of "format=`lascii`" in an "emp.sub.13 resume" table is executed. It is assumed that the "emp.sub.13 resume" table has previously been defined. First, a cursor "cl" is defined by "DECLARE CURSOR" and the retrieving condition is designated by "SELECT". The cursor "cl" is opened by next "OPEN" and one line adapted to the above condition is extracted by next "FETCH". Now assuming that the "resume" column is of the LOB type, a locator value indicative of the LOB data is set to ":resume".
EXEC SQL SET :buffer=SUBSTR(:resume, :start, :size);
It is a description indicating that a SUBSTR function is executed to the LOB data indicated by the ":resume" locator and the result is stored into a "buffer". The SUBSTR function is a process for extracting data within a range from a ":start" position to a ":size" size of the LOB data shown by the ":resume" locator.
EXEC SQL SET :resume="Hello !!";
It is a description indicating that "Hello !!" is set to the LOB data shown by the ":resume" locator (namely, data is substituted into variables).
EXEC SQL INSERT into emp.sub.13 resume values(:number,
It is a description indicating that an integer value of ":number" and the LOB data shown by the ":resume" locator are stored into the "emp.sub.13 resume" table (namely, an insertion of data into an actual database).
As for the LOB data, the operation as mentioned above can be performed.
ADT (Abstract Data Type) is also one of the main functions of the next SQL. ADT is a data type of a user definition in which a type defined by the user can be designated when a table is defined in a manner similar to a type of a system definition. Since the ADT type is a data type formed by the user and is a data type which is not known by the DB, it is difficult to extract the ADT type data by the UAP such as a C language. For this purpose, an operating method using an ADT locator is provided. In a manner similar to the case of the LOB type, an example of the operation for the ADT type data is shown below.
EXEC SQL CREATE VALUE TYPE real.sub.13 estate
It is a description indicating that a "real.sub.13 estate" type of the user definition is defined.
EXEC SQL CREATE VALUE TYPE address
EXEC SQL CREATE VALUE TYPE doc
It is a description indicating that an "address" type and a "doc" type are defined in a manner similar to the definition of the "real.sub.13 estate" type.
EXEC SQL CREATE TABLE real.sub.13 estate.sub.13 info
It is a description indicating that a "real.sub.13 estate.sub.13 info" table is defined. Particularly, a column of "proper" in the table is set to the "real.sub.13 estate" type of the user definition.
EXEC SQL BEGIN DECLARE SECTION
SQL TYPE IS ADT.sub.13 LOCATOR FOR real.sub.13 estate re.sub.13 locator;
SQL TYPE IS BLOB(2G) doc.sub.13 file;
struct{ short len; char data [1024]; } text_descr;
EXEC SQL END DECLARE SECTION;
Variables defined within a range from "SQL BEGIN DECLARE SECTION" to "END DECLARE SECTION" in the above description denote a definition of the variables which is used for the DB operation. "re.sub.13 locator" indicates the ADT locator.
EXEC SQL DECLARE rel CURSOR FOR
WHERE price&lt;400000 FOR UPDATE OF property;
EXEC SQL OPEN rel;
In the above description, a cursor "rel" is defined by "DECLARE CURSOR" and a retrieving condition is designated by "SELECT". A retrieving condition is a retrieval for obtaining "property" in the line of "price&lt;400000" of the "real.sub.13 estate.sub.13 info" table in order to update "property". After that, the cursor "rel" is opened by "OPEN".
 while(SQLCODE==O){ EXEC SQL FETCH rel INTO :re_locator; EXEC SQL SET :text_descr = text_description(:re_locator); EXEC SQL SET :doc_file = data(document(:re_locator)); EXEC SQL UPDATE real_estate_info SET property = :re_locator WHERE CURRENT OF rel; }
EXEC SQL CLOSE rel;
The operation which is executed in the above "while" sentence will now be sequentially explained. First, one line is extracted by the cursor "rel" by "FETCH". It denotes that the setting of the current position and the positioning of the ADT locator (a locator value indicative of the ADT data is set to ":re.sub.13 locator") are performed. The next "SET" sentence indicates that the operation to designate ":re.sub.13 locator" as an ADT locator and to extract "text.sub.13 description" of a variable length character train from "property" as a "real.sub.13 estate" type of the user definition is performed. The next "SET" sentence denotes that the operation to designate ":re.sub.13 locator" as an ADT locator, to extract "document" as a "doc" type from "property" as a "real.sub.13 estate" type of the user definition, to extract BLOB data "data" in the "doc" type data, and to transfer it to a client is executed. The next "UPDATE" indicates the execution of the operation to reflect the value of the ADT data designated by the ADT locator "re.sub.13 locator" to the DB ("real.sub.13 estate.sub.13 info" table). After the "while" sentence, the cursor "rel" is closed by "CLOSE".
The following operation can be executed with respect to the ADT data.
The locator has been disclosed in Don Chamberlin, "Using The New DB2", pp. 236-241, 1996.
On the other hand, in recent years, the application of the relational DBMS has been being progressed mainly with respect to a data process of business affairs, so that a database system which can scalably cope with increases in transaction amount and database amount exceeding a progress of a performance of a CPU or a disk capacity has been strongly required. In response to the request, a parallel DBMS has been proposed by various companies. With respect to a parallel DBMS architecture, for example, there have been proposed three systems as described in FIG. 1 of the article disclosed in "Nikkei Electronics", pages 101 to 114, Feb. 27, 1995. The three systems are a Shared-Everything system in which a main storage and a hard disk drive (HDD) are shared by all processors, a Shared-Disk system in which only the HDD is shared among the processors and the main storage is provided for every processor, and a Shared-Nothing system in which none of the main storage and the HDD is not shared among the processors.
In the parallel DBMS, servers are connected by a network and all of a notification of a control, a movement of data, and the like are performed by communication. Therefore, the movement of data of a large size such as LOB data causes an increase in communication load, so that a load of the whole system is raised.